package com.syf.kit.core.dao.sql;

import com.google.common.base.Joiner;
import com.syf.kit.core.dao.sql.parse.WildCharSQLParseResult;
import com.syf.kit.core.dao.sql.parse.WildCharSQLParseUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSession;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

@Slf4j
public class JdbcSQLProcessor implements SQLProcessor {
    private static final int DEFAULT_QUERY_RECORD_MAX_SIZE = 6000;

    private static final String MYSQL_DB_TYP = "MYSQL";

    private static final String ORACLE_DB_TYP = "ORACLE";

    private Connection connection;

    private SqlSession sqlSession;

    private boolean dirt = false;

    /**
     * MYSQL
     * ORACLE
     */
    private String dbType;

    public JdbcSQLProcessor(SqlSession sqlSession) {
        this.sqlSession = sqlSession;
        this.connection = sqlSession.getConnection();

        try {
            dbType = connection.getMetaData().getDatabaseProductName().toUpperCase(Locale.ROOT);
        } catch (SQLException e) {
            throw new RuntimeException("获取数据库类型出现异常", e);
        }
    }

    @Override
    public String getDbType() {
        return dbType;
    }

    /**
     * 执行通配符SQL（SQL中包含#{key-name}，${key-name}）
     *
     * @param wildCharSQL 动态SQL （SQL中包含#{key-name}，${key-name}）
     * @param paramMap    sql参数map，其中key是动态SQL中的占位符内name
     */
    public int executeWildCharSQL(String wildCharSQL, Map<String, Object> paramMap) {
        WildCharSQLParseResult result = WildCharSQLParseUtil.parseWildCharSQL(wildCharSQL, paramMap);
        Object[] params = new Object[result.getParamList().size()];
        for (int i = 0; i < result.getParamList().size(); i++) {
            params[i] = result.getParamList().get(i);
        }

        return executeSQL(result.getStandardSql(), params);
    }

    /**
     * 执行标准DML SQL.
     *
     * @param sql    标准DML SQL，例如：update talbe set col1=? where col2=?
     * @param params 是sql动态绑定参数，顺序与sql中动态绑定符一致
     */
    public int executeSQL(String sql, Object... params) {
        log.info("execute DML SQL ==> {}", sql);
        dirt = true;
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            setParam(ps, params);
            int result = ps.executeUpdate();
            // 由于有数据更新，让sqlSession清除1级缓存
            sqlSession.clearCache();
            log.info("execute DML SQL result is {}", result);
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(sql + " -- 数据库SQL执行异常", e);
        }
    }

    @Override
    public void executeBatch(String sql, List<Map<String, Object>> paramMaps) {
        WildCharSQLParseResult result = WildCharSQLParseUtil.parseWildCharSQL(sql, paramMaps.get(0));
        try (PreparedStatement ps = connection.prepareStatement(result.getStandardSql())) {
            for (int i = 0; i < paramMaps.size(); i++) {
                result = WildCharSQLParseUtil.parseWildCharSQL(sql, paramMaps.get(i));
                setParam(ps, result.getParamList());
                ps.addBatch();
                if (i % 500 == 0) {
                    //执行batch
                    ps.executeBatch();
                    //清空batch
                    ps.clearBatch();
                }
            }
            //执行batch
            ps.executeBatch();
            //清空batch
            ps.clearBatch();
        } catch (SQLException e) {
            throw new RuntimeException(sql + " -- 数据库SQL执行异常", e);
        }
    }

    /**
     * 采用通配符SQL查询单笔记录
     *
     * @param wildCharSQL 动态SQL （SQL中包含#{key-name}，${key-name}）
     * @param paramMap    sql参数map，其中key是动态SQL中的占位符内name
     */
    public Map<String, Object> readRecord(String wildCharSQL, Map<String, Object> paramMap) {
        WildCharSQLParseResult result = WildCharSQLParseUtil.parseWildCharSQL(wildCharSQL, paramMap);
        return readRecord(result.getStandardSql(), result.getParamList());
    }

    /**
     * 标准SQL查询单笔记录
     *
     * @param sql    标准查询sql
     * @param params 参数列表
     */
    public Map<String, Object> readRecord(String sql, Object... params) {
        List<Map<String, Object>> resultList = queryGroup(sql, new JdbcPageInfo(1), params);
        return resultList.size() == 0 ? null : resultList.get(0);
    }

    /**
     * 采用通配符SQL查询多笔（max size = QUERY_RECORD_MAX_SIZE）
     *
     * @param wildCharSQL 动态SQL （SQL中包含#{key-name}，${key-name}）
     * @param paramMap    sql参数map，其中key是动态SQL中的占位符内name
     */
    public List<Map<String, Object>> queryGroup(String wildCharSQL, Map<String, Object> paramMap) {
        return queryGroup(wildCharSQL, DEFAULT_QUERY_RECORD_MAX_SIZE, paramMap);
    }

    /**
     * 采用通配符SQL查询多笔（max size = limit）
     *
     * @param wildCharSQL 动态SQL
     * @param limit       max size
     */
    public List<Map<String, Object>> queryGroup(String wildCharSQL, int limit) {
        return queryGroup(wildCharSQL, limit, new HashMap<>());
    }

    /**
     * 采用标准SQL查询多笔（max size = QUERY_RECORD_MAX_SIZE）
     */
    public List<Map<String, Object>> queryGroup(String sql) {
        return queryGroup(sql, DEFAULT_QUERY_RECORD_MAX_SIZE, new HashMap<>());
    }

    /**
     * 采用通配符SQL查询多笔（max size = QUERY_RECORD_MAX_SIZE）
     *
     * @param wildCharSQL 动态SQL （SQL中包含#{key-name}，${key-name}）
     * @param paramMap    sql参数map，其中key是动态SQL中的占位符内name
     */
    public List<Map<String, Object>> queryGroup(String wildCharSQL, int limit, Map<String, Object> paramMap) {
        WildCharSQLParseResult result = WildCharSQLParseUtil.parseWildCharSQL(wildCharSQL, paramMap);
        return queryGroup(result.getStandardSql(), new JdbcPageInfo(limit), result.getParamList());
    }

    /**
     * 采用通配符SQL 分页查询多笔
     *
     * @param wildCharSQL 动态SQL （SQL中包含#{key-name}，${key-name}）
     * @param pageNum     当前页码
     * @param pageSize    显示条数
     * @param paramMap    sql参数map，其中key是动态SQL中的占位符内name
     * @return
     */
    @Override
    public JdbcPageInfo queryPage(String wildCharSQL, int pageNum, int pageSize, Map<String, Object> paramMap) {
        WildCharSQLParseResult result = WildCharSQLParseUtil.parseWildCharSQL(wildCharSQL, paramMap);
        int totalCount = 0;
        //页码等于1才需要查总条数，其它翻页总条数不需要变
        if (pageNum == 1) {
            totalCount = queryTotalCount(result.getStandardSql(), result.getParamList());
        }
        JdbcPageInfo page = new JdbcPageInfo(pageNum, pageSize, totalCount);
        List<Map<String, Object>> data = queryGroup(result.getStandardSql(), page, result.getParamList());
        page.setList(data);
        return page;
    }

    private List queryGroup(String sql, JdbcPageInfo page, Object... params) {
        List<Map<String, Object>> result = null;
        sql = sqlJoinLimit(sql, page);
        log.info("execute Query SQL ==> {}", sql);
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            setParam(ps, params);
            try (ResultSet rs = ps.executeQuery()) {
                result = ResultSetHandle.toListMap(rs);
//                rs.close();
            }
            log.info("execute Query SQL has {} records", result.size());
//            ps.close();
        } catch (SQLException e) {
            throw new RuntimeException(sql + " -- 数据库SQL执行异常", e);
        }
        return result;
    }

    /**
     * 为避免大查询将内存撑爆，查询多笔数据时，必须设置笔数限制
     *
     * @param sql   标准sql
     * @param limit 笔数限制
     */
    @Override
    public String sqlJoinLimit(String sql, int limit) {
        return sqlJoinLimit(sql, new JdbcPageInfo(limit));
    }

    /**
     * 为避免大查询将内存撑爆，查询多笔数据时，必须设置笔数限制
     *
     * @param sql  标准sql
     * @param page 笔数限制
     */
    private String sqlJoinLimit(String sql, JdbcPageInfo page) {
        if (sql.trim().endsWith("for update")) {
            return sql;
        }
        if (MYSQL_DB_TYP.equals(dbType)) {
            int beginIndex = (page.getPageNum() - 1) * page.getPageSize();
            return sql + " limit " + beginIndex + " , " + page.getPageSize();
        } else if (ORACLE_DB_TYP.equals(dbType)) {
            return sql + "< ROWNUM <= " + page.getPageSize();
        } else {
            return sql + "< ROWNUM <= " + page.getPageSize();
        }
    }

    @Override
    public int queryTotalCount(String sql, Object... params) {
        int totalCount = 0;
        StringBuffer countSql = new StringBuffer("select count(1) totalCount from ( ").append(sql).append(" ) bc");
        log.info("execute Query SQL ==> {}", countSql.toString());
        try (PreparedStatement ps = connection.prepareStatement(countSql.toString())) {
            setParam(ps, params);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                totalCount = rs.getInt(1);
//                rs.close(); // try(io)不用显示关闭
            }
            log.info("execute Query SQL has {} totalCount", totalCount);
//            ps.close();
        } catch (SQLException e) {
            throw new RuntimeException(sql + " -- 数据库SQL执行异常", e);
        }
        return totalCount;
    }

    @Override
    public boolean isDirt() {
        return dirt;
    }

    @Override
    public void resetDirt() {
        dirt = false;
    }

    /**
     * 设置jdbc 通配符参数
     *
     * @param ps
     * @param params
     * @throws SQLException
     */
    private void setParam(PreparedStatement ps, Object... params) throws SQLException {
        if (params.length == 0) {
            return;
        }
        if (params[0] instanceof List) {
            List paramList = (List) params[0];
            if (paramList.size() != 0) {
                for (int i = 0; i < paramList.size(); i++) {
                    ps.setObject(i + 1, paramList.get(i));
                }
            }

            log.info("params==> {}", Joiner.on(",").join(paramList));
        } else {
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            log.info("params ==> {}", Joiner.on(",").join(params));
        }
    }

}
